package com.ljqc.sbom.management.repository;


import com.alibaba.fastjson.JSONObject;
import com.ljqc.sbom.management.domain.SpdxLicense;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

public interface SpdxLicenseRepository extends JpaRepository<SpdxLicense, Integer> {

    @Query(nativeQuery = true, value = "SELECT risk_level level,license,count(1) count from spdx_license" +
            " where spdx_project_id=?1 group by license")
    List<Map<String, Object>> getLicenseAllRisk(Integer projectId);

    @Query(nativeQuery = true, value = "SELECT " +
            " license, " +
            " count( DISTINCT license ) license_count, " +
            " risk_level, " +
            " category  " +
            "FROM " +
            " spdx_license  " +
            "WHERE " +
            " spdx_project_id = ?1  " +
            "GROUP BY " +
            " risk_level")
    List<JSONObject> getLicenseBySpdxProjectId(Integer projectId);

    @Query(nativeQuery = true, value = "select MAX(risk_level) level from spdx_license WHERE spdx_project_id = ?1 ")
    Integer getMaxLicenseLeave(Integer projectId);

    @Query(nativeQuery = true, value = "select license,risk_level riskLevel,category, count(1) packageCount" +
            " from spdx_license " +
            " where spdx_project_id = ?1 " +
            " and if(coalesce(?2,null) is null,1=1,license like CONCAT('%', ?2 , '%'))" +
            " and if(coalesce (?3 , null) is null,1=1,risk_level = ?3 ) " +
            " GROUP BY license ",
            countQuery = "select count(*)" +
                    " from spdx_license " +
                    " where spdx_project_id = ?1 " +
                    " and if(coalesce(?2,null) is null,1=1,license like CONCAT('%', ?2 , '%'))" +
                    " and if(coalesce (?3 , null) is null,1=1,risk_level = ?3 ) " +
                    " GROUP BY license ")
    Page<JSONObject> getLicenseList(Integer projectId, String license, Integer riskLevel, Pageable page);

    @Query(nativeQuery = true, value = "select distinct license from spdx_license WHERE spdx_project_id = ?1 ")
    List<String> getLicenseAll(Integer projectId);

    @Transactional
    void deleteAllBySpdxProjectId(Integer projectId);
}
